﻿using BCCommon;
using BCCommon.TMS.Driver;
using BCEntity.TMS.Driver.DriverLearnDatum;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.Driver.DriverLearnDatum
{
    public class DriverLearnDatumData : IDriverLearnDatumData
    {
        private const string QUERYSQL = "SELECT A.DatumId,A.DatumTopic,A.DatumAnswerId,A.CreateTime,A.ModifyAdminId,A.ModifyDateTime,A.AppliedTo,B.AnswerContent,B.CreateTime as DaturnAnswerCreateTime,B.ModifyAdminId as DaturnAnswerModifyAdminId,B.ModifyDateTime as DaturnAnswerModifyDateTime " +
                                        "FROM  DriverLearnDatum A LEFT JOIN DriverLearnDatumAnswer B ON A.DatumAnswerId=B.DatumAnswerId ";
        private const string JOIN_QUERY_SQL = @"SELECT A.DatumId,A.DatumTopic,B.DatumAnswerId,B.AnswerContent,A.CreateTime,A.ModifyAdminId,A.ModifyDateTime,A.AppliedTo  
                                         FROM DriverLearnDatum A INNER JOIN DriverLearnDatumAnswer B on A.DatumAnswerId=B.DatumAnswerId";
        private readonly IDatabaseContext databaseContext;

        public DriverLearnDatumData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        /// <summary>
        /// 新增一个学习资料信息
        /// </summary>
        /// <param name="entity">学习资料信息</param>
        /// <returns></returns>
        public async Task<DriverLearnDatumEntity> InsertAsync(DriverLearnDatumEntity entity)
        {
            string sql = @"INSERT INTO DriverLearnDatum(DatumTopic,DatumAnswerId,CreateTime,ModifyAdminId,ModifyDateTime,AppliedTo) 
                           VALUES(?DatumTopic,?DatumAnswerId,?CreateTime,?ModifyAdminId,?ModifyDateTime,?AppliedTo); 
                           SELECT DatumId,DatumAnswerId,DatumTopic,CreateTime,ModifyAdminId,ModifyDateTime,AppliedTo FROM  DriverLearnDatum WHERE DatumId=LAST_INSERT_ID();";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DatumTopic", entity.DatumTopic, DbType.AnsiString, size: 200);
            parameters.Add("?DatumAnswerId", entity.DatumAnswerId, DbType.Int32);
            parameters.Add("?ModifyAdminId", entity.ModifyAdminId, DbType.Int64);
            parameters.Add("?CreateTime", entity.CreateTime, DbType.DateTime);
            parameters.Add("?ModifyDateTime", entity.ModifyDateTime, DbType.DateTime);
            parameters.Add("?AppliedTo", entity.AppliedTo.ToString(), DbType.AnsiString, size: 50);
            return await databaseContext.Master.QueryFirstOrDefaultAsync<DriverLearnDatumEntity>(sql, parameters);

        }
        /// <summary>
        /// 检查学习资料
        /// </summary>
        /// <param name="datumId">学习资料Id</param>
        /// <returns></returns>

        public async Task<bool> CheckExistsByIdAsync(int datumId)
        {
            string sql = @"SELECT 1 FROM DriverLearnDatum WHERE DatumId=?DatumId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DatumId", datumId, DbType.Int32);
            var result = await databaseContext.Master.ExecuteScalarAsync(sql, parameter);
            return result != null;
        }

        /// <summary>
        /// 删除一个学习资料信息
        /// </summary>
        /// <param name="datumId">学习资料Id</param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(int datumId)
        {
            string sql = @"DELETE FROM DriverLearnDatum WHERE DatumId=?DatumId";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DatumId", datumId, DbType.Int32);
            var result = await databaseContext.Master.ExecuteAsync(sql, parameter);
            return result >0;
        }

        /// <summary>
        /// 获取一条指定的学习资料
        /// </summary>
        /// <param name="datumId">学习资料Id</param>
        /// <returns></returns>
        public async Task<DriverLearnDatumEntity> GetDriverLearnDatumByIdAsync(int datumId)
        {
            string sql = string.Format(@"{0} WHERE A.DatumId=?DatumId", QUERYSQL);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DatumId", datumId, DbType.Int32);
            return await databaseContext.Master.QueryFirstOrDefaultAsync<DriverLearnDatumEntity>(sql, parameter);
        }

        /// <summary>
        /// 分页获取司机学习资料
        /// </summary>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="pageSize">分页页数</param>
        /// <param name="appliedTo">适用业务类型</param>
        /// <returns></returns>
        public async Task<(IEnumerable<DriverLearnDatumEntity>, int)> GetDriverLearnDatumsAsync(string datumTopic, string answerContent,
            DriverRoleType? appliedTo, int pageIndex, int pageSize)
        {
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            int startIndex = (pageIndex - 1) * pageSize;
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (appliedTo.HasValue)
            {
                wheres.Add(" A.AppliedTo=?AppliedTo");
                parameters.Add("?AppliedTo", appliedTo.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(datumTopic))
            {
                wheres.Add(" A.DatumTopic LIKE ?DatumTopic");
                parameters.Add("?DatumTopic", $"%{datumTopic}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(answerContent))
            {
                wheres.Add(" B.AnswerContent LIKE ?AnswerContent");
                parameters.Add("?AnswerContent", $"%{answerContent}%", DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} LIMIT {2}, {3}; 
                                SELECT COUNT(1) FROM DriverLearnDatum A 
                                INNER JOIN DriverLearnDatumAnswer B on A.DatumAnswerId=B.DatumAnswerId
                                        {1}",
                JOIN_QUERY_SQL,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                startIndex,
                pageSize);

            return await databaseContext.QueryMultipleAsync<DriverLearnDatumEntity, int>(sql, parameters);
        }
    }
}
